package com.application.DataProvider;

import java.sql.Date;
import java.util.ArrayList;

import com.application.DTO.LearnerDTO;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class LearnerDAO{

	Context _mcontext = null;

	public LearnerDAO(Context context){
		this._mcontext = context;
	}

	
	
	public boolean isExist(int id)
	{
		DataProvider provider = new DataProvider(_mcontext);
		SQLiteDatabase db = provider.openDataBase();
		boolean result = false;
		Cursor cur = null;
		try 
		{
			String sql = "Select count(*) from Learner where ID=" +  id;
			cur = provider.execQuery(db, sql);
			while (cur.moveToNext()) {
				int i = Integer.valueOf(cur.getString(0));
				if(i != 0)
					result = true;
			}
		} catch (Exception e) {
			// TODO: handle exception
		} finally{
			cur.close();
			db.close();
		}
		return result;
	}

	public ArrayList<LearnerDTO> getLearner_idClass(int IDClass)
	{
		ArrayList<LearnerDTO> learnerList = new ArrayList<LearnerDTO>();
		DataProvider provider = new DataProvider(_mcontext);
		SQLiteDatabase db = provider.openDataBase();
		Cursor cur = null;
		try 
		{
			String sql = "Select * from Learner where idClass = " + IDClass;
			cur = provider.execQuery(db, sql);
			while (cur.moveToNext()) {
				LearnerDTO lDto = new LearnerDTO();
				lDto.set_ID(cur.getInt(0));
				lDto.set_Fullname(cur.getString(1));
				lDto.setBirthday(Date.valueOf(cur.getString(2)));
				lDto.set_Address(cur.getString(3));
				lDto.set_Phone(cur.getString(4));
				lDto.set_Username(cur.getString(5));
				lDto.set_IDClass(cur.getInt(6));
				learnerList.add(lDto);
			}
		} catch (Exception e) {
			// TODO: handle exception
		} finally{
			cur.close();
			db.close();
		}

		return learnerList;
	}
	
	public LearnerDTO getLearner(String userName)
	{
		LearnerDTO lDto = new LearnerDTO();
		DataProvider provider = new DataProvider(_mcontext);
		SQLiteDatabase db = provider.openDataBase();
		Cursor cur = null;
		try 
		{
			String sql = "Select * from Learner where userName = " + userName;
			cur = provider.execQuery(db, sql);
			while (cur.moveToNext()) {
				lDto.set_ID(cur.getInt(0));
				lDto.set_Fullname(cur.getString(1));
				lDto.setBirthday(Date.valueOf(cur.getString(2)));
				lDto.set_Address(cur.getString(3));
				lDto.set_Phone(cur.getString(4));
				lDto.set_Username(cur.getString(5));
				lDto.set_IDClass(cur.getInt(6));
			}
		} catch (Exception e) {
			// TODO: handle exception
		} finally{
			cur.close();
			db.close();
		}

		return lDto;
	}
	
	public LearnerDTO getLearner(int ID)
	{
		LearnerDTO lDto = new LearnerDTO();
		DataProvider provider = new DataProvider(_mcontext);
		SQLiteDatabase db = provider.openDataBase();
		Cursor cur = null;
		try 
		{
			String sql = "Select * from Learner where id = " + ID;
			cur = provider.execQuery(db, sql);
			while (cur.moveToNext()) {
				lDto.set_ID(cur.getInt(0));
				lDto.set_Fullname(cur.getString(1));
				lDto.setBirthday(Date.valueOf(cur.getString(2)));
				lDto.set_Address(cur.getString(3));
				lDto.set_Phone(cur.getString(4));
				lDto.set_Username(cur.getString(5));
				lDto.set_IDClass(cur.getInt(6));
			}
		} catch (Exception e) {
			// TODO: handle exception
		} finally{
			cur.close();
			db.close();
		}

		return lDto;
	}
	
	public int getIDClass(int id)
	{
		int result = 0;
		DataProvider provider = new DataProvider(_mcontext);
		SQLiteDatabase db = provider.openDataBase();
		Cursor cur = null;
		try 
		{
			String sql = "Select IDClass from Learner where ID = " + id;
			cur = provider.execQuery(db, sql);
			while (cur.moveToNext()) {
				result = cur.getInt(0);
			}
		} catch (Exception e) {
			// TODO: handle exception
		} finally{
			cur.close();
			db.close();
		}
		Log.d("PEERDROID","Result : " + result);
		return result;
	}
	
	public int getIDClass(String userName)
	{
		int result = 0;
		DataProvider provider = new DataProvider(_mcontext);
		SQLiteDatabase db = provider.openDataBase();
		Cursor cur = null;
		try 
		{
			String sql = "Select IDClass from Learner where UserName = '" + userName + "'";
			cur = provider.execQuery(db, sql);
			while (cur.moveToNext()) {
				result = cur.getInt(0);
			}
		} catch (Exception e) {
			// TODO: handle exception
		} finally{
			cur.close();
			db.close();
		}
		Log.d("PEERDROID","Result : " + result);
		return result;
	}
	public String getFullName(int id)
	{
		String result = null;
		DataProvider provider = new DataProvider(_mcontext);
		SQLiteDatabase db = provider.openDataBase();
		Cursor cur = null;
		try 
		{
			String sql = "Select FullName from Learner where ID = " + id;
			cur = provider.execQuery(db, sql);
			while (cur.moveToNext()) {
				result = cur.getString(0);
			}
		} catch (Exception e) {
			// TODO: handle exception
		} finally{
			cur.close();
			db.close();
		}
		Log.d("PEERDROID","Result : " + result);
		return result;
	}
	
	public String getUserName(int id)
	{
		String result = null;
		DataProvider provider = new DataProvider(_mcontext);
		SQLiteDatabase db = provider.openDataBase();
		Cursor cur = null;
		try 
		{
			String sql = "Select UserName from Learner where ID = " + id;
			cur = provider.execQuery(db, sql);
			while (cur.moveToNext()) {
				result = cur.getString(0);
			}
		} catch (Exception e) {
			// TODO: handle exception
		} finally{
			cur.close();
			db.close();
		}
		Log.d("PEERDROID","Result : " + result);
		return result;
	}
	
	//Insert du lieu vao bang Learner
	public int insertLearner(LearnerDTO lDto) {
		Log.d("PEERDROID","insert chap");
		int result = 0;
		DataProvider provider = new DataProvider(_mcontext);
		SQLiteDatabase db = provider.openDataBase();
		try {
			ContentValues values = new ContentValues();
			values.put("ID", lDto.get_ID());
			values.put("FullName", lDto.get_Fullname());
			values.put("Birthday", lDto.getBirthday().toString());
			values.put("Address", lDto.get_Address());
			values.put("Phone", lDto.get_Phone());
			values.put("UserName",lDto.get_Username());
			values.put("IDClass",lDto.get_IDClass());

			result = provider.execInsertCommand(db, "Learner", values);
		} catch (Exception e) {
			// TODO: handle exception
		} finally {
			db.close();
		}

		return result;
	}

	//Cap nhat bang Learner
	public int updateLearner(LearnerDTO lDto)
	{
		int result=0;
		DataProvider provider = new DataProvider(_mcontext);
		SQLiteDatabase db = provider.openDataBase();
		try{
			ContentValues values = new ContentValues();
			values.put("FullName", lDto.get_Fullname());
			values.put("Birthday", lDto.getBirthday().toString());
			values.put("Address", lDto.get_Address());
			values.put("Phone", lDto.get_Phone());
			values.put("UserName",lDto.get_Username());
			values.put("IDClass",lDto.get_IDClass());

			String whereClause="ID=?";
			String[]whereArgs = {String.valueOf(lDto.get_ID())};
			result= provider.execUpdateCommand(db, "Learner", values, whereClause, whereArgs);

		} catch (Exception e) {
			// TODO: handle exception
		}finally {
			db.close();
		}

		return result;
	}

	//Delete doi tuong LearnerDTO
	public int deleteLearner(int idLearner)
	{
		int result=0;
		DataProvider provider = new DataProvider(_mcontext);
		SQLiteDatabase db = provider.openDataBase();
		try{	
			String whereClause="ID=?";
			String[]whereArgs = {String.valueOf(idLearner)};	
			result=provider.execDeleteCommand(db, "Learner", whereClause, whereArgs);
		} catch (Exception e) {
			// TODO: handle exception
		}
		finally {
			db.close();
		}
		return result;
	}
}
